__version__ = "$Revision: 30 $"
__date__ = "$Date: 2011-07-17 15:03:13 +0000 (Sun, 17 Jul 2011) $"
__lastChangedBy__ = "$Author: wolters@3sconsult.de $"
__repository__ = "$HeadURL: http://sir3spy.googlecode.com/svn/tags/2011-07-17/svn/Sir3sPy/Utils/trunk/getLPipeData-cx_Oracle.py $"

"""
example
   - reading pipe data from a SIR 3S database 
"""

import os
import sys
import logging
from optparse import OptionParser,OptionGroup 
import cx_Oracle
import re

# configure logging ...    
moduleName,ext=os.path.splitext(os.path.basename(__file__))     
loggerName='sir3spy.'+moduleName 
logging.getLogger(loggerName).addHandler(logging.NullHandler())

logFileName = os.path.join(os.getcwd(),moduleName+'.log')        
logging.basicConfig(filename=logFileName,filemode='w',level=logging.ERROR,format="%(asctime)s ; %(name)-50s ; %(levelname)-7s ; %(message)s")
   
def getData(connectionStr,selectStr):
    """
    return: 
            - header
                 a 1-dim list of tuples with column meta data
            - data
               - a 1-dim list of tuples with column data  
    """

    logger = logging.getLogger(loggerName+'.getData')    
    
    try:        
        # get connection
        con = cx_Oracle.connect(conStr)
        cur = con.cursor()

        # get data
        logger.info('selectStr: {:s}'.format((re.sub(r'(LEFT  JOIN)',r'\n\1',re.sub(r'(INNER JOIN)',r'\n\1',re.sub(',','\n,',selectStr))))))
        cur.execute(selectStr) 
        header=cur.description                  
        data = cur.fetchall()

        # close connection
        cur.close()

    except cx_Oracle.DatabaseError as exc:
        header = None
        data = None       
        errorMsg, =  exc.args
        errorMsg = str(errorMsg).rstrip('\n')     
        logger.error('DatabaseError: {:s}'.format(errorMsg))                 
    except:
        header = None
        data = None
        logger.error('Exception.')
        raise   
    finally:
        return header,data 

selectStr ="SELECT\
      M.MTEXT                                              MId\
     ,M.MTEXT                                              MTxt\
     ,M.NETZTYP                                            MTyp\
     ,M.INST                                               MInst\
     ,M.PROJEKT                                            MProj\
     ,M.PLANER                                             MPlan\
     ,TS.fKDE                                              MfkBV\
     ,OS.fkDE                                              MfkZ\
     \
     ,'ROHR'                                               OTyp\
     ,TS.BESCHREIBUNG                                      OTxt\
     ,TS.IDREFERENZ                                        OIdRef\
     ,TS.pk                                                OpkBV\
     ,TS.rk                                                OrkBV\
     ,OS.pk                                                OpkZ\
     \
     ,xSIR3SCONTIND(CONT.ID,CONT.IDPARENT)                  OCoInd\
     ,SIR3SCONTNAME(CONT.ID\
     ,CONT.IDPARENT,CONT.NAME,M.BEZEICHNER)                OCoNam\
     ,CONT.ID                                              OCoID\
     \
     ,TS.IPLANUNG                                          OPlInd\
     ,TS.KENNUNG                                           OKeInd\
     \
     ,CASE WHEN OS.IRTRENN = 0 THEN 1 ELSE 0 END           OAcInd\
     ,OS.LAMBDA                                            fphiNW\
     ,(1-OS.KANTENZV)*8760                                 eR\
     ,ROUND(ABS(.5*(OS.QMI+OS.QMK)),8)                     fAbs\
     ,ROUND(ABS(.5*(OS.VI+OS.VK)),8)                       vAbs\
     ,CASE\
      WHEN .5*(OS.QMI+OS.QMK) IS NULL OR .5*(OS.QMI+OS.QMK)=0\
      THEN 0\
      WHEN .5*(OS.QMI+OS.QMK)>0\
      THEN 1 ELSE -1 END                                   fSign\
     \
     ,TS.fkKI                                              NOpkBVi\
     ,NIOS.pk                                              NOpkZi\
     ,NI.NAME                                              NIdi\
     ,NI.IDREFERENZ                                        NIdRefi\
     ,NI.XKOR                                              Xi\
     ,NI.YKOR                                              Yi\
     ,NI.ZKOR                                              Zi\
     ,PZI.NAME                                             PZi\
     ,FSFI.NAME                                            FSFi\
     ,STFI.NAME                                            STFi\
     ,UTZI.NAME                                            UTZi\
     ,LFKTI.NAME                                           NQLfi\
     ,NI.IPLANUNG                                          NPlIndi\
     ,NI.KENNUNG                                           NKeIndi\
     \
     ,TS.fkKK                                              NOpkBVk\
     ,NKOS.pk                                              NOpkZk\
     ,NK.NAME                                              NIdk\
     ,NK.IDREFERENZ                                        NIdRefk\
     ,NK.XKOR                                              Xk\
     ,NK.YKOR                                              Yk\
     ,NK.ZKOR                                              Zk\
     ,PZK.NAME                                             PZk\
     ,FSFK.NAME                                            FSFk\
     ,STFK.NAME                                            STFk\
     ,UTZK.NAME                                            UTZk\
     ,LFKTK.NAME                                           NQLfk\
     ,NK.IPLANUNG                                          NPlIndk\
     ,NK.KENNUNG                                           NKeIndk\
     \
     ,ROUND(NIOS.QM,6)                                     Qi\
     ,ROUND(NIOS.P,6)                                      Pi\
     ,ROUND(NIOS.TM,6)                                     Ti\
     ,NIOS.RHO                                             rhoi\
     ,ROUND(NIOS.WALTER,6)                                 Ai\
     ,(1-NIOS.ZV)*8760                                     Ri\
     ,NIOS.ESQUELLSP                                       Si\
     ,NIOS.DYNVISKO                                        KVi\
     \
     ,ROUND(NKOS.QM,6)                                     Qk\
     ,ROUND(NKOS.P,6)                                      Pk\
     ,ROUND(NKOS.TM,6)                                     Tk\
     ,NKOS.RHO                                             rhok\
     ,ROUND(NKOS.WALTER,6)                                 Ak\
     ,(1-NKOS.ZV)*8760                                     Rk\
     ,NKOS.ESQUELLSP                                       Sk\
     ,NKOS.DYNVISKO                                        KVk\
     \
     ,CASE WHEN BI.fkCONT IS NOT NULL THEN 1 ELSE 0 END    NContIndi\
     ,CASE WHEN BI.fkCONT IS NOT NULL THEN\
      SIR3SCONTNAME(\
      CONTBI.ID,CONTBI.IDPARENT,CONTBI.NAME,M.BEZEICHNER) \
      ELSE ''\
      END                                                  NContNamei\
     \
     ,CASE WHEN BK.fkCONT IS NOT NULL THEN 1 ELSE 0 END    NContIndk\
     ,CASE WHEN BK.fkCONT IS NOT NULL THEN \
      SIR3SCONTNAME(\
      CONTBK.ID,CONTBK.IDPARENT,CONTBK.NAME,M.BEZEICHNER) \
      ELSE ''\
      END                                                  NContNamek\
 FROM\
     ROHR_BZ             OS\
     INNER JOIN ROHR     TS     ON OS.fk       = TS.pk\
     \
     INNER JOIN MODELL   M      ON OS.fkDE     = M.fkBZ\
     INNER JOIN CONT            ON TS.fkCONT   = CONT.pk\
     \
     INNER JOIN KNOT     NI     ON TS.fkKI     = NI.pk\
     INNER JOIN KNOT     NK     ON TS.fkKK     = NK.pk\
     INNER JOIN KNOT_BZ  NIOS   ON NI.pk       = NIOS.fk\
     INNER JOIN KNOT_BZ  NKOS   ON NK.pk       = NKOS.fk\
     INNER JOIN FSTF     FSFI   ON NI.fkFSTF   = FSFI.pk\
     INNER JOIN FSTF     FSFK   ON NK.fkFSTF   = FSFK.pk\
     INNER JOIN STOF     STFI   ON FSFI.fkSTOF = STFI.pk\
     INNER JOIN STOF     STFK   ON FSFK.fkSTOF = STFK.pk\
     INNER JOIN PZON     PZI    ON NI.fkPZON   = PZI.pk\
     INNER JOIN PZON     PZK    ON NK.fkPZON   = PZK.pk\
     INNER JOIN UTMP     UTZI   ON NI.fkUTMP   = UTZI.pk\
     INNER JOIN UTMP     UTZK   ON NK.fkUTMP   = UTZK.pk\
     INNER JOIN LFKT     LFKTI  ON NIOS.fkLFKT = LFKTI.pk\
     INNER JOIN LFKT     LFKTK  ON NKOS.fkLFKT = LFKTK.pk\
     \
     LEFT  JOIN VKNO     BI     ON TS.fkKI     = BI.fkKNOT\
     LEFT  JOIN CONT     CONTBI ON BI.fkCONT   = CONTBI.pk\
     \
     LEFT  JOIN VKNO     BK     ON TS.fkKK     = BK.fkKNOT\
     LEFT  JOIN CONT     CONTBK ON BK.fkCONT   = CONTBK.pk\
 WHERE\
     TS.pk <> '-1' AND (TS.DELETED IS NULL OR TS.DELETED = 0)"

if __name__ == "__main__":

    try:
                            
        logger = logging.getLogger(loggerName)
        
        # configure command line options ...
        parser = OptionParser(usage="%prog <Database> [-v|-q] -u USER -p PASSWORD -s SERVER")
        
        optGrpLog = OptionGroup(parser,"Log Options")
        optGrpLog.add_option("-v", action="store_true" , dest="verbose", default=True, help="logging on")
        optGrpLog.add_option("-q", action="store_false", dest="verbose",help="logging off")    
        parser.add_option_group(optGrpLog)
        
        optGrpSrv = OptionGroup(parser, "Server Options")
        optGrpSrv.add_option("-u", "--user", help="User"
                      ,action="store", type="string", dest="user", metavar="USER") 
        optGrpSrv.add_option("-p", "--password", help="Password"
                      ,action="store", type="string", dest="password", metavar="PASSWORD") 
        optGrpSrv.add_option("-s", "--server", help="Server"
                      ,action="store", type="string", dest="server", metavar="SERVER")        
        parser.add_option_group(optGrpSrv)    
        
        # default return value
        returnValue=0
        # -1: exception
        # -2: command line not ok
        
        # parse command line
        (options, argsLeftOver) = parser.parse_args()

        # configure logging from command line
        if options.verbose:            
            logger.setLevel(logging.INFO)
        
        # check command line
        if len(argsLeftOver)>0:
            print ('option parse: keyless arguments are not defined')
            print ('usage information: '+os.path.basename(sys.argv[0])+' '+'--help')     
            returnValue=-2       
            sys.exit(returnValue)   
        if options.user==None or options.password==None or options.server==None:
            print ('options required: -u USER -p PASSWORD -s SERVER')
            print ('usage information: '+os.path.basename(sys.argv[0])+' '+'--help')     
            returnValue=-2       
            sys.exit(returnValue)  
             
        # connection string
        conStr=options.user+'/'+options.password+'@'+options.server
        logger.info('conStr: {:s}'.format(conStr))        
        
        # get the data
        header,data = getData(conStr,selectStr)

        logger.info('header: {:s}'.format(header))
        # print data
        for row in data:
           logger.info('row: {:s}'.format(row))            

    except SystemExit:        
        pass    
    except:
        returnValue=-1
        raise
    finally:              
        if returnValue==-1:
            exInfo='EXEPTION:'
        elif returnValue<-1:
            exInfo='FAILure:'
        else:
            exInfo='ok:'    
        logger.info('argv: {:s} returnValue: {:d}'.format(sys.argv[1:],returnValue))                                
        sys.exit(returnValue)        